# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
from matplotlib.font_manager import FontProperties

# 设置中文字体路径（请根据实际路径调整）
font_path = r'fonts/SIMSUN.TTC'
font_prop = FontProperties(fname=font_path)

# 创建输出文件夹
output_folder = r'output/44'
os.makedirs(output_folder, exist_ok=True)

# 输入数据
data = {
    '日期': ['2021/10/1', '2021/10/2', '2021/10/3', '2021/10/4', '2021/10/5', '2021/10/6', '2021/10/7'],
    '展现量': [26032, 42356, 37427, 37840, 41590, 36879, 43754],
    '点击量': [1120, 1953, 1564, 1261, 1673, 1202, 2403],
    '点击率': [4.30, 4.61, 4.18, 3.33, 4.02, 3.26, 5.49],
    '成交笔数': [36, 76, 51, 39, 66, 37, 99],
    '成交金额': [1765, 2432, 2299, 1879, 3168, 1850, 4236],
    '直通车花费': [928, 1006, 972, 941, 1256, 899, 1595]
}

df = pd.DataFrame(data)

# 计算点击转化率、投入产出比、投入产出比环比增长率
df['点击转化率'] = (df['成交笔数'] / df['点击量']).round(4) * 100
df['投入产出比'] = (df['成交金额'] / df['直通车花费']).round(2)
df['投入产出比环比增长率'] = df['投入产出比'].pct_change().fillna(0).round(4) * 100

# 计算2021年国庆节活动期间汇总数据
summary_2021 = df[['展现量', '点击量', '成交笔数', '成交金额', '直通车花费']].sum()
summary_2021['点击率'] = summary_2021['点击量'] / summary_2021['展现量'] * 100
summary_2021['点击转化率'] = summary_2021['成交笔数'] / summary_2021['点击量'] * 100
summary_2021['投入产出比'] = summary_2021['成交金额'] / summary_2021['直通车花费']

# 2020年国庆节活动期间数据
summary_2020 = pd.Series({
    '展现量': 209869,
    '点击量': 8965,
    '点击率': 4.27,
    '成交笔数': 296,
    '成交金额': 14236,
    '直通车花费': 6698,
    '点击转化率': 296 / 8965 * 100,
    '投入产出比': 14236 / 6698
})

# 计算同比增长率
growth_rate = (summary_2021 - summary_2020) / summary_2020 * 100
growth_rate['点击率'] = (summary_2021['点击率'] - summary_2020['点击率']) / summary_2020['点击率'] * 100
growth_rate['点击转化率'] = (summary_2021['点击转化率'] - summary_2020['点击转化率']) / summary_2020['点击转化率'] * 100
growth_rate['投入产出比'] = (summary_2021['投入产出比'] - summary_2020['投入产出比']) / summary_2020['投入产出比'] * 100

# 简答题：识别异常值
abnormal_days = df[df['投入产出比环比增长率'] < -10]['日期'].tolist()
if abnormal_days:
    print(f"异常的投入产出比数据出现在以下日期：{', '.join(abnormal_days)}")
else:
    print("没有发现异常的投入产出比数据。")

# 单选题：识别异常的同比增长率
negative_growth_rates = growth_rate[growth_rate < 0].dropna().index.tolist()
if negative_growth_rates:
    print(f"异常的同比增长率为负数的数据指标是：{', '.join(negative_growth_rates)}")
else:
    print("没有发现异常的同比增长率为负数的数据指标。")

# 分析结论
analysis_conclusion = (
    "从2021年国庆节店铺活动期间直通车推广效果来看，整体表现良好。"
    "相较于2020年同期，多数指标都有所增长，特别是在成交金额和点击转化率方面。"
    "然而，需要特别关注的是，在某些天内投入产出比出现了较大幅度的下降，这可能是由于市场竞争加剧或其他外部因素导致。"
    "建议进一步分析这些天的具体情况，以优化未来的推广策略。"
)

print(f"分析结论:\n{analysis_conclusion}")

# 格式化数据为百分比或保留两位小数
df['点击转化率'] = df['点击转化率'].apply(lambda x: f'{x:.2f}%')
df['投入产出比'] = df['投入产出比'].apply(lambda x: f'{x:.2f}')
df['投入产出比环比增长率'] = df['投入产出比环比增长率'].apply(lambda x: f'{x:.2f}%')

summary_2021['点击率'] = f'{summary_2021["点击率"]:.2f}%'
summary_2021['点击转化率'] = f'{summary_2021["点击转化率"]:.2f}%'
summary_2021['投入产出比'] = f'{summary_2021["投入产出比"]:.2f}'

summary_2020['点击率'] = f'{summary_2020["点击率"]:.2f}%'
summary_2020['点击转化率'] = f'{summary_2020["点击转化率"]:.2f}%'
summary_2020['投入产出比'] = f'{summary_2020["投入产出比"]:.2f}'

growth_rate['点击率'] = f'{growth_rate["点击率"]:.2f}%'
growth_rate['点击转化率'] = f'{growth_rate["点击转化率"]:.2f}%'
growth_rate['投入产出比'] = f'{growth_rate["投入产出比"]:.2f}%'

# 输出结果
print("\n2021年国庆节活动期间汇总数据:")
print(summary_2021)

print("\n2020年国庆节活动期间汇总数据:")
print(summary_2020)

print("\n同比增长率:")
print(growth_rate)

# 将结果保存到新的 Excel 文件
output_excel_file = os.path.join(output_folder, '直通车推广数据监控表.xlsx')

with pd.ExcelWriter(output_excel_file, engine='xlsxwriter') as writer:
    # 将原始数据写入 Excel 文件
    df.to_excel(writer, sheet_name='每日数据', index=False)
    
    # 写入2021年国庆节活动期间汇总数据
    summary_2021_df = pd.DataFrame([summary_2021]).T.reset_index()
    summary_2021_df.columns = ['指标', '2021年']
    summary_2021_df.to_excel(writer, sheet_name='汇总数据', index=False, startrow=0)

    # 写入2020年国庆节活动期间汇总数据
    summary_2020_df = pd.DataFrame([summary_2020]).T.reset_index()
    summary_2020_df.columns = ['指标', '2020年']
    summary_2020_df.to_excel(writer, sheet_name='汇总数据', index=False, startrow=len(summary_2021_df) + 2)

    # 写入同比增长率
    growth_rate_df = pd.DataFrame([growth_rate]).T.reset_index()
    growth_rate_df.columns = ['指标', '同比增长率']
    growth_rate_df.to_excel(writer, sheet_name='汇总数据', index=False, startrow=len(summary_2021_df) + len(summary_2020_df) + 4)

print(f"\n包含分析结果的新 Excel 文件已保存到 {output_excel_file}")